/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2016
 */

package com.jzwl.xydk.wap.business.home.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.xydk.wap.business.home.pojo.BannerInfo;
import com.jzwl.xydk.wap.business.home.pojo.Special;
import com.jzwl.xydk.wap.business.home.pojo.UserBasicinfo;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillImage;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillclassF;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillclassS;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillinfo;

@Repository("homeDao")
public class HomeDao {

	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库

	public List<UserSkillclassF> findUserSkillclassF() {
		String sql = "select * from `xiaoka-xydk`.user_skillclass_father t where t.isDelete = 0 and t.status = 1 order by t.ord limit 0,100";
		List<UserSkillclassF> cityInfos = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillclassF.class));
		return cityInfos;
	}

	//获取优质资源的个数
	public Map<String, Object> getSpecialNum() {
		Map<String, Object> resMap = new HashMap<String, Object>();
		String sql = "select dd.dic_value as dicValue from `xiaoka`.v2_dic_type t LEFT JOIN `xiaoka`.v2_dic_data dd ON t.dic_id = dd.dic_id where t.isDelete = 0 and t.dic_code = 'SpecialNum'";
		resMap = baseDAO.queryForMap(sql);
		return resMap;
	}
	//根据数据的类型获取字典值
	public Map<String, Object> getDataValueByType(String type) {
		Map<String, Object> resMap = new HashMap<String, Object>();
		String sql = "select dd.dic_value as dicValue from `xiaoka`.v2_dic_type t LEFT JOIN `xiaoka`.v2_dic_data dd ON t.dic_id = dd.dic_id where t.isDelete = 0 and t.dic_code = '"+type+"'";
		resMap = baseDAO.queryForMap(sql);
		return resMap;
	}

	//获取热搜词管理
	public List<Map<String, Object>> getDataByTypeCode(String dicCode) {
		String sql = "select t.id as id,t.dic_name as dicName,t.dic_value as dicValue from v2_dic_data t"
				+ " inner join v2_dic_type d on t.dic_id=d.dic_id where t.isDelete=0 and d.dic_code='" + dicCode + "'";

		return baseDAO.queryForList(sql);
	}

	public List<Special> getSpecial(String num) {
		String sql = "select * from `xiaoka-xydk`.special t where t.isDelete = 0 and t.isDisplay = 1 order by t.ord limit 0,"
				+ num;
		List<Special> cityInfos = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(Special.class));
		return cityInfos;
	}

	public List<BannerInfo> getBannerInfoList() {
		String sql = "select t.* from `xiaoka-xydk`.banner_info t where bannerType = 1 and  isDelete = 0 and isUse = 1 order by t.ord asc limit 0,100";
		List<BannerInfo> infos = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(BannerInfo.class));
		return infos;
	}

	public Map<String, Object> getUserAllNum() {

		Map<String, Object> resMap = new HashMap<String, Object>();

		String sql = "select COUNT(id) as allNum from `xiaoka-xydk`.user_basicinfo t where t.isDelete = 0 ";

		resMap = baseDAO.queryForMap(sql);

		return resMap;
	}

	public List<UserBasicinfo> findNewUserBasicinfo(String num) {
		String sql = "SELECT "
				+ "	b.id,b.userName,b.nickname,b.cityId,b.schoolId,b.phoneNumber,b.email,b.wxNumber,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,b.headPortrait,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b " + " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " where b.isDelete = 0 and skillStatus=0 and b.auditState = 1 ORDER BY b.createDate desc LIMIT 0,"+num;
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));
		return users;
	}

	public List<UserBasicinfo> findUserBasicinfo(Map<String, Object> map) {
		String sql = "SELECT"
				+ "	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.wxNumber,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
				+ " FROM "
				+ " `xiaoka-xydk`.user_basicinfo b "
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " where b.isDelete = 0 and skillStatus=0 and b.auditState = 1  order by b.createDate desc";

		if (null != map.get("start") && StringUtils.isNotEmpty(map.get("start").toString())) {
			sql = sql + " LIMIT " + map.get("start") + ",6";

		}

		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));
		return users;
	}

	/**
	 * 获取入住用户的基本信息
	 * @param map
	 * @return
	 */
	public UserBasicinfo getUserInfo(Map<String, Object> map) {
		String sql = "SELECT"
				+ "	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.viewNum,b.wxNumber,b.headPortrait,b.nickname,wc.headImgUrl as headImgUrl,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,b.backPicture,c.cityName,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b "
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId " + " where b.isDelete = 0  ";
		if (null != map.get("id") && StringUtils.isNotEmpty(map.get("id").toString())) {
			sql = sql + " and b.id = " + map.get("id") + "";
		}
		if (null != map.get("openId") && StringUtils.isNotEmpty(map.get("openId").toString())) {
			sql = sql + " and b.openId = '" + map.get("openId") + "'";
		}
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));

		if (users != null && users.size() > 0) {
			return users.get(0);
		}
		return null;
	}

	/**
	 * 获取入住用户的基本信息 自己查询自己
	 * @param map
	 * @return
	 */
	public UserBasicinfo getUserInfoselfVersion2(Map<String, Object> map) {
		String sql = "SELECT"
				+ "	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.viewNum,b.wxNumber,wc.headImgUrl as headImgUrl,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b "
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId " + " where b.isDelete = 0 ";
		if (null != map.get("openId") && StringUtils.isNotEmpty(map.get("openId").toString())) {
			sql = sql + " and b.openId = '" + map.get("openId") + "'";
		}
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));

		if (users != null) {
			return users.get(0);
		}
		return null;
	}

	/**
	 * 获取入住用户的基本信息
	 * @param map
	 * @return
	 */
	public UserBasicinfo getUserInfo(String id) {
		String sql = "SELECT"
				+ "	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.viewNum,b.wxNumber,wc.headImgUrl as headImgUrl,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b "
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId " + " where b.isDelete = 0 and b.id = "
				+ id;
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));

		if (users != null) {
			return users.get(0);
		}
		return null;
	}

	public List<UserSkillinfo> getUserSkillInfos(String basicId) {
		String sql = "SELECT" + "	b.*,t.className as className" + " FROM " + "`xiaoka-xydk`.user_skillinfo b "
				+ " left join `xiaoka-xydk`.user_skillclass_father t on b.skillFatId = t.id"
				+ " where b.isDelete = 0 and b.basicId = " + basicId + " order by b.createDate desc";
		List<UserSkillinfo> kills = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillinfo.class));
		return kills;
	}

	/**
	 * 获取技能的详情图片
	 * @param skillId
	 * @return
	 */
	public List<UserSkillImage> getSkillImages(String skillId) {
		String sql = "SELECT" + "	* " + " FROM " + "`xiaoka-xydk`.user_skill_image b "
				+ " where b.imgtype = 1 and b.skillId = '" + skillId + "'";
		List<UserSkillImage> skillImages = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillImage.class));
		return skillImages;
	}

	/**
	 * 获取数据，用于分页
	 * @param map
	 * @return
	 */
	public PageObject getUserSkillInfo(Map<String, Object> map) {
		try {

			String sql = "select * " + " from `xiaoka-xydk`.user_basicinfo t " + " where t.isDelete=0 ";

			sql = sql + " order by t.createDate desc ";
			PageObject po = baseDAO.queryForMPageList(sql.toString(), new Object[] {}, map);
			return po;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * get分类的子级
	 * @param fid
	 * @return
	 */
	public List<UserSkillclassS> getSkillClassS(Map<String, Object> map) {
		String sql = "SELECT" + "	*,t.id as sid " + " FROM " + "`xiaoka-xydk`.user_skillclass_son t "
				+ " where t.isDelete = 0 and status = 1 ";

		if (null != map.get("fid") && StringUtils.isNotEmpty(map.get("fid").toString())) {
			sql = sql + "and t.fatherId = " + map.get("fid");
		}
		sql = sql + " order by t.ord asc limit 0,99";
		List<UserSkillclassS> skillClass = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillclassS.class));
		return skillClass;
	}

	/**
	 * 获取入住用户的基本信息
	 * @param map
	 * @return
	 */
	public List<UserSkillinfo> getUserSkillInfoList(Map<String, Object> map) {
		String sql = "SELECT"
				+ " t.id as id,t.basicId as basicId,t.skillName as skillName,t.viewNum as viewNum,t.serviceType,wc.headImgUrl as headImgUrl,t.skillPrice as skillPrice,t.skillDepict as skillDepict,b.userName as userName,b.openId as openId,c.cityName as cityName,si.schoolName as schoolName,sl.dicDataId as schoolLabel,uss.className as className"
				+ " FROM " + " `xiaoka-xydk`.user_skillinfo t "
				+ " LEFT JOIN `xiaoka-xydk`.user_basicinfo b ON t.basicId = b.id"
				+ " LEFT JOIN `xiaoka-xydk`.user_skillclass_son uss ON t.skillSonId = uss.id"
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " LEFT JOIN `xiaoka-xydk`.schoollabel sl ON sl.schoolId = si.id "
				+ " where b.isDelete = 0 and skillStatus=0 and b.auditState = 1 and t.isDelete = 0 and t.isDisplay = 0  ";

		if (null != map.get("sid") && StringUtils.isNotEmpty(map.get("sid").toString())) {
			if (!"0".equals(map.get("sid"))) {
				sql = sql + " and t.skillSonId  = " + map.get("sid") + "";
			}

		}
		if (null != map.get("fid") && StringUtils.isNotEmpty(map.get("fid").toString())) {
			sql = sql + " and t.skillFatId  = " + map.get("fid") + "";

		}
		

		////添加筛选
		boolean isPage = true;

		sql = "select * from (" + sql + ")  r where 1=1 ";

		if (null != map.get("maxPrice") && StringUtils.isNotEmpty(map.get("maxPrice").toString())) {
			sql = sql + " and r.skillPrice < " + map.get("maxPrice");
		}

		if (null != map.get("minPrice") && StringUtils.isNotEmpty(map.get("minPrice").toString())) {
			sql = sql + " and r.skillPrice > " + map.get("minPrice");
		}

		if (null != map.get("cityName") && StringUtils.isNotEmpty(map.get("cityName").toString())) {
			sql = sql + " and r.cityName like '%" + map.get("cityName") + "%' ";
		}

		if (null != map.get("schoolName") && StringUtils.isNotEmpty(map.get("schoolName").toString())) {
			sql = sql + " and r.schoolName like '%" + map.get("schoolName") + "%' ";
		}

		/*if (null != map.get("searchValue") && StringUtils.isNotEmpty(map.get("searchValue").toString())) {
			sql = sql + " and r.userName like '%" + map.get("searchValue") + "%' || r.skillName like '%"
					+ map.get("searchValue") + "%' ";
		}*/

		if (null != map.get("topValue") && StringUtils.isNotEmpty(map.get("topValue").toString())) {
			sql = sql + " and r.skillName like '%" + map.get("topValue") + "%' ";
			//isPage = false;
		}
		
		if (null != map.get("serviceType") && StringUtils.isNotEmpty(map.get("serviceType").toString())) {
			sql = sql + " and r.serviceType like '%" + map.get("serviceType") + "%' ";
			//isPage = false;
		}
		
		if (null != map.get("schoolLabel") && StringUtils.isNotEmpty(map.get("schoolLabel").toString())) {
			sql = sql + " and r.schoolLabel like '%" + map.get("schoolLabel") + "%' ";
			//isPage = false;
		}
		
		if (null != map.get("topValue") && StringUtils.isNotEmpty(map.get("topValue").toString())) {
			sql = sql + " and r.skillName like '%" + map.get("topValue") + "%' ";
			//isPage = false;
		}
		
		if (null != map.get("searchValue") && StringUtils.isNotEmpty(map.get("searchValue").toString())) {
			sql = sql + " and r.skillName like '%" + map.get("searchValue")+ "%' ";
		}

		if (null != map.get("orderPrice") && StringUtils.isNotEmpty(map.get("orderPrice").toString())) {
			sql = sql + " order by r.skillPrice " + map.get("orderPrice");
		}
		
		

		if (isPage) {
			sql = sql + " limit " + map.get("start") + ","+map.get("pageSize")+"";
		}
		
		System.out.println("sql===========================:"+sql);

		List<UserSkillinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillinfo.class));
		return users;
	}

	/**
	 * 根据技能的id获取技能的信息
	 * @param map
	 * @return
	 * 
	 * private String className;
	
	private int viewNum;
	
	private String headImgUrl;
	 */
	public UserSkillinfo getSkillInfo(Map<String, Object> map) {
		String sql = "SELECT"
				+ " t.id as id,t.basicId as basicId,t.skillName as skillName,t.skillPrice as skillPrice,t.skillDepict as skillDepict,"
				+ " t.videoUrl as videoUrl,t.viewNum as viewNum,t.textUrl as textUrl,t.serviceType as serviceType,t.otherOpus as otherOpus,b.userName as userName,b.openId as openId,b.headPortrait as headPortrait,"
				+ " usf.className as className,wc.headImgUrl as headImgUrl,c.cityName as cityName,si.schoolName as schoolName"
				+ " FROM " + " `xiaoka-xydk`.user_skillinfo t "
				+ " left join `xiaoka-xydk`.user_skillclass_father usf on t.skillFatId = usf.id"
				+ " LEFT JOIN `xiaoka-xydk`.user_basicinfo b ON t.basicId = b.id"
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId " + " where b.isDelete = 0  and t.id="
				+ map.get("id");

		List<UserSkillinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillinfo.class));
		if (users != null) {
			return users.get(0);
		}
		return null;
	}

	public boolean updateViewNum(Map<String, Object> map) {

		String sql = "update `xiaoka-xydk`.user_basicinfo b set " + " viewNum=:viewNum" + " where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public boolean updateSkillViewNum(Map<String, Object> map) {

		String sql = "update `xiaoka-xydk`.user_skillinfo b set " + " viewNum=:viewNum" + " where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}

	/**
	 * 获取临时的用户的头像和昵称
	 * @param basicId
	 * @return
	 */
	public Map<String, Object> getInfo(Long basicId) {
		String sql = "SELECT" + "	t.picUrl as headImgUrl" + " FROM " + "`xiaoka-xydk`.user_temporary t "
				+ " where  t.userId = " + basicId;

		return baseDAO.queryForMap(sql);
	}

	/*
	 * 根据字典查出线上还是线下
	 * @dxf
	 * @return List
	 */
	public List<Map<String, Object>> getServiceTypeVersion2() {
		try {
			String sql = "SELECT dt.id dataId,dt.dic_id dicId,dt.dic_name,dt.dic_value,dt.isDelete FROM v2_dic_data dt INNER JOIN v2_dic_type tp ON dt.dic_id=tp.dic_id"
					+ " WHERE tp.dic_code='serviceType' AND dt.isDelete=0 AND tp.isDelete=0";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 根据字典查出热搜词
	 * @dxf
	 * @return List
	 */
	public List<Map<String, Object>> getHotWordVersion2() {
		try {
			String sql = "SELECT dt.id dataId,dt.dic_id dicId,dt.dic_name,dt.dic_value,dt.isDelete FROM v2_dic_data dt INNER JOIN v2_dic_type tp ON dt.dic_id=tp.dic_id"
					+ " WHERE tp.dic_code='HotWord' AND dt.isDelete=0 AND tp.isDelete=0";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 查出211,985类的院校
	 * @dxf
	 * @return List
	 */
	public List<Map<String, Object>> getScholTypeListVersion2() {
		try {
			String sql = "SELECT scol.id schooId,scol.cityId,scol.schoolName,dcDa.id dicDataId,dcDa.dic_name,dcDa.dic_value "
					+ " FROM   `xiaoka-xydk`.schoollabel sclb INNER JOIN v2_dic_data dcDa ON dcDa.id=sclb.dicDataId"
					+ " INNER JOIN tg_school_info scol ON scol.id=sclb.schoolId";
			return baseDAO.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 优化资源
	 * dxf
	 * return List
	 */
	public PageObject queryOptimizaVersion2(Map<String, Object> map) {
		String sql = "select  id as id, title as title, content as content, imgerUrl as imgerUrl, ord as ord, isDisplay as isDisplay, "
				+ " createDate as createDate, isDelete as isDelete "
				+ " from `xiaoka-xydk`.special t where 1=1 AND t.isDelete = 0 AND t.isDisplay = 1 ";

		sql = sql + " order by id desc";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}

	/**
	 * 执行筛选
	 * dxf
	 * @param map
	 * @return List
	 */

	public List<UserSkillinfo> getUserSkillInfoListVersion2(Map<String, Object> map) {
		String sql = "SELECT"
				+ " t.id as id,t.basicId as basicId,t.skillName as skillName,t.viewNum as viewNum,wc.headImgUrl as headImgUrl,t.skillPrice as skillPrice,t.skillDepict as skillDepict,b.userName as userName,b.openId as openId,c.cityName as cityName,si.schoolName as schoolName"
				+ " FROM " + " `xiaoka-xydk`.user_skillinfo t "
				+ " LEFT JOIN `xiaoka-xydk`.user_basicinfo b ON t.basicId = b.id"
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " where b.isDelete = 0 and skillStatus=0 and b.auditState = 1 and t.isDelete = 0   ";

		if (null != map.get("sid") && StringUtils.isNotEmpty(map.get("sid").toString())) {
			if (!"0".equals(map.get("sid"))) {
				sql = sql + " and t.skillSonId  = " + map.get("sid") + "";
			}

		}
		if (null != map.get("fid") && StringUtils.isNotEmpty(map.get("fid").toString())) {
			sql = sql + " and t.skillFatId  = " + map.get("fid") + "";

		}

		////添加筛选
		boolean isPage = true;

		sql = "select * from (" + sql + ")  r where 1=1 ";

		if (null != map.get("maxPrice") && StringUtils.isNotEmpty(map.get("maxPrice").toString())) {
			sql = sql + " and r.skillPrice < " + map.get("maxPrice");
		}

		if (null != map.get("minPrice") && StringUtils.isNotEmpty(map.get("minPrice").toString())) {
			sql = sql + " and r.skillPrice > " + map.get("minPrice");
		}

		if (null != map.get("cityName") && StringUtils.isNotEmpty(map.get("cityName").toString())) {
			sql = sql + " and r.cityName like '%" + map.get("cityName") + "%' ";
		}

		if (null != map.get("schoolName") && StringUtils.isNotEmpty(map.get("schoolName").toString())) {
			sql = sql + " and r.schoolName like '%" + map.get("schoolName") + "%' ";
		}

		if (null != map.get("searchValue") && StringUtils.isNotEmpty(map.get("searchValue").toString())) {
			sql = sql + " and r.userName like '%" + map.get("searchValue") + "%' || r.skillName like '%"
					+ map.get("searchValue") + "%' ";
		}

		if (null != map.get("topValue") && StringUtils.isNotEmpty(map.get("topValue").toString())) {
			sql = sql + " and r.skillName like '%" + map.get("topValue") + "%' ";
			isPage = false;
		}

		if (null != map.get("orderPrice") && StringUtils.isNotEmpty(map.get("orderPrice").toString())) {
			sql = sql + " order by r.skillPrice " + map.get("orderPrice");
		}

		if (isPage) {
			sql = sql + " limit " + map.get("start") + ",10";
		}

		List<UserSkillinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserSkillinfo.class));

		return users;
	}

	/**
	 * 获取当前用户的Headimgurl信息,服务列表
	 * @param openId
	 * @return
	 */
	public Map<String, Object> getHeadimgurl(String openId) {

		Map<String, Object> resMap = new HashMap<String, Object>();
		String sql="SELECT cst.openId,cst.wxname,cst.headImgUrl,ifo.auditState,ifo.id " 
				+ " FROM  `xiaoka-xydk`.user_basicinfo ifo LEFT JOIN v2_wx_customer cst ON ifo.openId = cst.openId"
		        + " WHERE  cst.openId ='"+openId+"' ";
		resMap = baseDAO.queryForMap(sql);
		return resMap;

	}
	
	/**
	 * 获取当前用户的头像及昵称
	 * @param openId
	 * @return
	 */
	public Map<String, Object> getPersonInform(String openId) {

		Map<String, Object> resMap = new HashMap<String, Object>();
		String sql="SELECT cst.openId,cst.wxname,cst.headImgUrl " 
				+ " FROM   v2_wx_customer cst "
		        + " WHERE  cst.openId ='"+openId+"' ";
		resMap = baseDAO.queryForMap(sql);
		return resMap;

	}

	/**
	 * 根据OpenId来获取
	 * User的信息一级页面的展示
	 * @param openId
	 * @return
	 */
	public UserBasicinfo getUserinfoByOpenId(String openId) {
		String sql = "SELECT"
				+ "	b.id,b.userName,b.cityId,b.schoolId,b.phoneNumber,b.email,b.viewNum,b.wxNumber,wc.headImgUrl as headImgUrl,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b "
				+ " LEFT JOIN `xiaoka`.v2_wx_customer wc ON b.openId = wc.openId"
				+ " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " where b.isDelete = 0 and b.openId = '" + openId + "'";
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));
		if (users.size() != 0) {
			return users.get(0);
		}
		return null;
	}

	/**
	 * 创建人 cfz
	 * 创建时间：2017年2月22日14:25:40
	 * 根据UserId获取当前的skillId
	 * @param Sting Id 用户ID
	 */
	public String getSkillIdById(String id) {

		try {
			String sql = "select id from user_skillinfo where basicId = ?";
			String skillId = (baseDAO.getJdbcTemplate().query(sql, new Object[] { id },
					ParameterizedBeanPropertyRowMapper.newInstance(String.class))).toString();
			return skillId;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	//回显个人基本信息
	@SuppressWarnings("unchecked")
	public Map<String, Object> getByopenId(Map<String, Object> map) {
		Map<String, Object> resMap = new HashMap<String, Object>();
		String sql = "select * from `xiaoka-xydk`.user_info_edit where openId = '" + map.get("openId") + "'";
		System.out.println("打印回显基本信息："+sql);
		resMap = baseDAO.queryForMap(sql);
		return resMap;
	}

	//修改个人基本信息sql
	public boolean upPersonInform(Map<String, Object> map) {
		//查询有没有这个人
		map.put("id", Sequence.nextId());
		List<Map<String, Object>> listqry = null;
		String qursql = "select openId from  `xiaoka-xydk`.user_info_edit where openId='" + map.get("openId") + "'";
		listqry = baseDAO.queryForList(qursql);
		if (listqry.size() > 0) {
			String upsql = "UPDATE `xiaoka-xydk`.user_info_edit set  ";
			if (null != map.get("name") && StringUtils.isNotEmpty(map.get("name").toString())) {
				upsql = upsql + " name=:name ,";
			}
			if (null != map.get("phoneNumber") && StringUtils.isNotEmpty(map.get("phoneNumber").toString())) {
				upsql = upsql + " phoneNumber=:phoneNumber ,";
			}
			if (null != map.get("email") && StringUtils.isNotEmpty(map.get("email").toString())) {
				upsql = upsql + " email=:email ,";
			}
			upsql = upsql.substring(0, upsql.length() - 1);
			upsql = upsql + " where openId=:openId";
			System.out.println("更新数据的SQL："+upsql);
			return baseDAO.executeNamedCommand(upsql, map);
		} else {
			String addSql = "insert into `xiaoka-xydk`.user_info_edit " + " (id,name,phoneNumber,email,openId) " + " values "
					+ " (:id,:name,:phoneNumber,:email,:openId)";
			return baseDAO.executeNamedCommand(addSql, map);
		}

	}
	/**
	 * 获取更多优质资源 分页
	 * 创建人：cfz
	 * 创建时间：2017年2月22日17:21:05
	 * @param begin 初始条数
	 * @param end	中止条数
	 * @return
	 */
	public List<Special> getMoreSpecial(Map<String, Object> map) {
		
		String sql = "SELECT * ,DATE_FORMAT(sp.createDate,'%Y-%m-%d') as getdate FROM `xiaoka-xydk`.special sp limit "+map.get("start")+","+map.get("end");//测试查询全部数据
		System.out.println("---------------"+sql+"-------------");
		List<Special> specialList = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(Special.class));
		return specialList;
	}
	
	/**
	 * 根据表名获取当前的条数
	 * 创建人：cfz
	 * 创建时间：2017年2月22日17:21:05
	 * @param TableName 表名
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public int getTableCount(String tableName){
		String sql = "select count(*) from"+tableName;
		return baseDAO.getJdbcTemplate().queryForInt(sql);
	}
	
	/**
	 * 更多大咖
	 * 获取入驻用户的基本信息以及联表信息
	 * 创建人：cfz
	 * 创建时间：2017年2月22日20:25:36
	 */
	public List<UserBasicinfo> findMorePeople(Map<String, Object> map) {
		String sql = "SELECT "
				+ "	b.id,b.userName,b.nickname,b.cityId,b.schoolId,b.phoneNumber,b.email,b.wxNumber,b.openId,b.aboutMe,b.auditState,b.createDate,b.createUser,b.isRecommend,b.skillStatus,c.cityName,b.headPortrait,si.schoolName"
				+ " FROM " + "`xiaoka-xydk`.user_basicinfo b " + " LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id "
				+ " LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ " where b.isDelete = 0 and skillStatus=0 and b.auditState = 1 ORDER BY b.createDate desc LIMIT "+map.get("start")+","+map.get("end");
		List<UserBasicinfo> users = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(UserBasicinfo.class));
//		System.out.println("当前执行的SQL打印----------"+sql+"结束打印------------------");
		return users;
	}
	
	/**
	 * 根据表名获取当前的条数
	 * 创建人：cfz
	 * 创建时间：2017年2月22日17:21:05
	 * @param TableName 表名
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public int getCountPeople(){
		String sql = "SELECT COUNT(*) FROM `xiaoka-xydk`.user_basicinfo "
				+ "b LEFT JOIN `xiaoka`.tg_city c ON b.cityId = c.id LEFT JOIN `xiaoka`.tg_school_info si ON si.id = b.schoolId "
				+ "WHERE b.isDelete = 0 AND skillStatus = 0 AND b.auditState = 1";
		return baseDAO.getJdbcTemplate().queryForInt(sql);
	}
	
	/**
	 * 根据User_basicID 获取当前用户是否
	 * 创建人：cfz
	 * 创建时间：2017年2月26日18:19:55
	 * flage false 默认用户是没有入驻的
	 */
	public boolean getIsExist(String id){
		boolean flage = false;
		String sql = "SELECT COUNT(*) FROM `xiaoka-xydk`.user_basicinfo uso where uso.openId='"+id+"'";
		int count = baseDAO.getJdbcTemplate().queryForInt(sql);
		if(count>0){
			flage = true;
		}
		return flage;
	}
	
	
	/**
	 * 更新资源表的查看数量
	 * 处理结果为MAP<String(省),List<String>(市)>
	 * 创建人：cfz
	 * 创建时间：2017年2月27日15:48:35
	 */
	public boolean toAddViewNum(String id){
		boolean flage = true;
		String sql = "update `xiaoka-xydk`.special set  viewNum = viewNum+1 where id=?"; 
		try {
			baseDAO.getJdbcTemplate().update(sql,new Object[]{id});
		} catch (Exception e) {
			flage = false;
			System.out.println("更新内容失败");
		}
		return flage;
	}
	
	/**
	 * 更新资源表的查看数量
	 * 处理结果为MAP<String(省),List<String>(市)>
	 * 创建人：cfz
	 * 创建时间：2017年2月27日15:48:35
	 */
	public String toShowJump(String id){
		String sp ="";
		String sql = "select jumpLink from `xiaoka-xydk`.special where id=?"; 
		try {
			sp = (String) baseDAO.getJdbcTemplate().queryForObject(sql,new Object[]{id}, String.class);
		} catch (Exception e) {
			System.out.println("查询内容失败");
		}
		return sp;
	}
	/**
	 * 点赞或者取消赞的功能  true 为点赞 false 为取消赞
	 * @param flage 表示点赞或者是取消赞
	 * @param id 表示当前资源的ID值
	 * @return
	 */
	public boolean toAddIsagree(boolean flage,String id){
		String sql = "";
		boolean result = true;//默认为执行成功
		try {
			if(flage){
				sql = "update `xiaoka-xydk`.special set isagree=isagree+1 where id = ?";
			}else{
				sql = "update `xiaoka-xydk`.special set isagree=isagree-1 where id = ?";
			}
		} catch (Exception e) {
			result = false;
		}
		baseDAO.getJdbcTemplate().update(sql,new Object[]{id});
		return result;
	}
}
